 # -*- coding: utf-8 -*-
"""
Created on Mon Aug  7 09:17:39 2023

@author: tyshixi08
"""

import pandas as pd 
from datetime import date  
import numpy as np
import matplotlib.pyplot as plt
import math     
from sqlalchemy import create_engine
import statsmodels.api as sm
import Dfactor_get_data as get_data
from tqdm import tqdm
import rqdatac

def factor_pre(df):
    # 去异常值：3sigma法去极值
    def extreme_3sigma(dt,n=3):
        mean = np.nanmean(dt)           # 截面数据均值    
        std = np.nanstd(dt)             # 截面数据标准差
        dt_up = mean + n*std       # 上限
        dt_down = mean - n*std     # 下限
        return dt.clip(dt_down, dt_up, axis = 0)   # 超出上下限的值，赋值为上下限
    
    # Zscore标准化
    def standardize_z(dt):
        mean = np.nanmean(dt, axis = 0)     #  截面数据均值
        std = np.nanstd(dt, axis = 0)       #  截面数据标准差
        return (dt - mean)/std
    output = pd.DataFrame()
    test_day = df.t_date.unique()
    for i in tqdm(range(test_day.shape[0])):
        temp_data = df[df.t_date == test_day[i]]
        temp_data = temp_data.copy()
        temp_data["value"] = standardize_z(temp_data["value"])
        temp_data["value"] = extreme_3sigma(temp_data["value"])
        #df_after[df_after.Date == test_day[-2]] = temp_data
        output = pd.concat([output,temp_data])
    return output.sort_index()

class convfactor_calculation(object):
    def __init__(self, start_date, end_date, freq):
        data_all = pd.read_csv(r'data/data_after_filter.csv',sep=',',header='infer')
        data_all['Date'] = pd.to_datetime(data_all["Date"])
        data_all = data_all.sort_values(by = "Date")
        date_all = data_all["Date"].unique()
        
        df_after = data_all
        df_valuation_stock = pd.read_csv(r'data/data_all.csv',sep=',',header='infer')
        df_valuation_stock.Date = pd.to_datetime(df_valuation_stock.Date)
        df_valuation_stock = df_valuation_stock.sort_values(by = "Date")
        df_valuation_stock = df_valuation_stock[df_valuation_stock.n_close != 0] # 濒临退市转债收盘价为0干扰计算
        df_valuation_stock = df_valuation_stock[df_valuation_stock.n_convValue != 0] # 中间停牌可能导致转股价为0,直接剔除
        df_valuation_stock['n_convValue'] = df_valuation_stock.groupby(['c_bondCode'])['n_convValue'].fillna(method='ffill')
        
        df_after = df_after.sort_values(by = "Date")
        df_after = df_after[(df_after["Date"] >= pd.to_datetime(start_date)) & (df_after["Date"] <= pd.to_datetime(end_date))]
        df_after = df_after[df_after.n_convValue != 0] # 中间停牌可能导致转股价为0,直接剔除
        
        last_day = df_after['Date'].unique()
        last_day = last_day[list(range(0,len(last_day),freq))]
        df_after = df_after[df_after["Date"].isin(last_day)]
        date_n = last_day.shape[0]
        
        self.df_valuation_stock = df_valuation_stock
        self.df_after =df_after
        self.last_day = last_day
        self.date_n = date_n
        self.start_date = start_date
        self.end_date = end_date
        self.freq = freq
      
#%% 简单转债类因子
    def fconvsize(self):
        #转债余额 convsize
        df = self.df_after[["Date", "c_bondCode", "n_outstandingBalance","n_close"]]
        df = df.rename(columns = {"Date": "t_date", "c_bondCode":"c_code", "n_outstandingBalance":"value"})
        df[["t_date", "c_code", "value"]] =factor_pre(df[["t_date", "c_code", "value"]])
        return df[["t_date", "c_code", "value"]]
    
    def fconv_stocksize(self):
        #转债余额占正股市值比重 conv_stocksize
        df = self.df_after[["Date", "c_bondCode", "n_outstandingBalance", "S_VAL_MV","n_close"]]
        df = df.copy()
        df["value"] = df["n_outstandingBalance"]/df["S_VAL_MV"]*10000*100
        df = df.rename(columns = {"Date": "t_date", "c_bondCode":"c_code"})
        df[["t_date", "c_code", "value"]] =factor_pre(df[["t_date", "c_code", "value"]])
        return df[["t_date", "c_code", "value"]]
    
    def fconvprice(self):
        #转债价格 price
        df = self.df_after[["Date", "c_bondCode", "n_close"]]
        df = df.rename(columns = {"Date": "t_date", "c_bondCode":"c_code", "n_close":"value"})
        df[["t_date", "c_code", "value"]] = factor_pre(df[["t_date", "c_code", "value"]])
        return df[["t_date", "c_code", "value"]]
    
    def ts_price(self, ND):
        # 转债价格分位数 ts_price_rollNDtdays_1d
        df = self.df_after[['Date', 'c_bondCode', 'n_close']].copy()
        df_valuation_stock = self.df_valuation_stock[['Date', 'c_bondCode', 'n_close']].copy()
        df_valuation_stock = df_valuation_stock.set_index('Date')
        # 滚动ND个数据点计算
        min_max_cp = df_valuation_stock.groupby('c_bondCode').rolling(ND).agg({'n_close': ['min', 'max']}).reset_index()
        min_max_cp.columns = ['c_bondCode', 'Date', 'min_sec_price', 'max_sec_price']
        df = pd.merge(df, min_max_cp, on=['c_bondCode', 'Date'], how='left')
        df['ts_price_roll{}tdays_1d'.format(ND)] = (df['n_close'] - df['min_sec_price']) / (df['max_sec_price'] - df['min_sec_price'])
        df = df.rename(columns={'Date': 't_date', 'c_bondCode': 'c_code', 'ts_price_roll{}tdays_1d'.format(ND): 'value'})
        return factor_pre(df[["t_date", "c_code", "value"]])
    
    def fconvChange(self): 
        #换手率convChange
        df = self.df_after[["Date", "c_bondCode", "n_amt", "n_outstandingBalance","n_close"]]
        df = df.copy()
        df["value"] = df.n_amt/df.n_outstandingBalance/10**8
        df = df.rename(columns = {"Date": "t_date", "c_bondCode":"c_code"})
        df[["t_date", "c_code", "value"]] =factor_pre(df[["t_date", "c_code", "value"]])
        return df[["t_date", "c_code", "value"]]

    def ts_turnover(self, ND):
        # 换手率分位数 ts_turnover_rollNDtdays_1d
        df = self.df_after[['Date', 'c_bondCode', 'n_amt', 'n_outstandingBalance']].copy()
        df['sec_turnover'] = df['n_amt'] / df['n_outstandingBalance'] / 10 ** 8
        df_valuation_stock = self.df_valuation_stock[['Date', 'c_bondCode', 'n_amt', 'n_outstandingBalance']].copy()
        df_valuation_stock['sec_turnover'] = df_valuation_stock['n_amt'] / df_valuation_stock['n_outstandingBalance'] / 10 ** 8
        df_valuation_stock = df_valuation_stock.set_index('Date')
        # 滚动ND个数据点计算
        min_max_cp = df_valuation_stock.groupby('c_bondCode').rolling(ND).agg({'sec_turnover': ['min', 'max']}).reset_index()
        min_max_cp.columns = ['c_bondCode', 'Date', 'min_sec_turnover', 'max_sec_turnover']
        df = pd.merge(df, min_max_cp, on=['c_bondCode', 'Date'], how='left')
        df['ts_turnover_roll{}tdays_1d'.format(ND)] = (df['sec_turnover'] - df['min_sec_turnover']) / (df['max_sec_turnover'] - df['min_sec_turnover'])
        df = df.rename(columns={'Date': 't_date', 'c_bondCode': 'c_code', 'ts_turnover_roll{}tdays_1d'.format(ND): 'value'})
        return factor_pre(df[["t_date", "c_code", "value"]])
    
    def fconv_amplitude(self):
        #振幅 conv_amplitude
        df = self.df_after[["Date", "c_bondCode", "n_high", "n_low", "n_close", "n_pctChange"]]
        df = df.copy() 
        df["value"] = (df.n_high - df.n_low) / (df.n_close / (1+df.n_pctChange/100))*100
        df = df.rename(columns = {"Date": "t_date", "c_bondCode":"c_code"})
        df[["t_date", "c_code", "value"]] =factor_pre(df[["t_date", "c_code", "value"]])
        return df[["t_date", "c_code", "value"]]
    
    def ts_amplitude(self, ND):
        # 振幅分位数 ts_amplitude_rollNDtdays_1d
        def cal_adjust(df_slice):
            df_slice = df_slice.copy()
            df_slice = df_slice.sort_values(['Date'])
            df_slice = df_slice.fillna(method = 'ffill')
            return df_slice
        
        df = self.df_after[['Date', 'c_bondCode', 'n_close', 'n_high', 'n_low', 'n_pctChange']].copy()
        df_valuation_stock = self.df_valuation_stock[['Date', 'c_bondCode', 'n_close', 'n_high', 'n_low', 'n_pctChange']].copy()
        df_valuation_stock['sec_amplitude'] = (df_valuation_stock['n_high'] - df_valuation_stock['n_low']) / (df_valuation_stock['n_close'] / (1 + df_valuation_stock['n_pctChange']))
        df_valuation_stock = df_valuation_stock.groupby('c_bondCode').apply(cal_adjust).reset_index()  
        df_valuation_stock = df_valuation_stock.set_index('Date')
        # 滚动ND个数据点计算
        min_max_cp = df_valuation_stock.groupby('c_bondCode').rolling(ND).agg({'sec_amplitude': ['min', 'max']}).reset_index()
        min_max_cp.columns = ['c_bondCode', 'Date', 'min_amplitude', 'max_amplitude']
        df_valuation_stock = pd.merge(df_valuation_stock, min_max_cp, on=['c_bondCode', 'Date'], how='left')
        df_valuation_stock['ts_amplitude_roll{}tdays_1d'.format(ND)] = (df_valuation_stock['sec_amplitude'] - df_valuation_stock['min_amplitude']) / (df_valuation_stock['max_amplitude'] - df_valuation_stock['min_amplitude'])
        df = pd.merge(df, df_valuation_stock[['c_bondCode','Date','ts_amplitude_roll{}tdays_1d'.format(ND)]], on=['c_bondCode', 'Date'], how='left')
        df = df.rename(columns={'Date': 't_date', 'c_bondCode': 'c_code', 'ts_amplitude_roll{}tdays_1d'.format(ND): 'value'})
        return factor_pre(df[['t_date', 'c_code', 'value']])
    
    def famplitude_delta(self):
        #振幅偏差 amplitude_delta
        df = self.df_after
        df = df.copy()
        df["value"] = (df.S_DQ_HIGH - df.S_DQ_LOW) / (df.S_DQ_CLOSE / (1+df.S_DQ_PCTCHANGE/100))*100 - (df.n_high - df.n_low) / (df.n_close / (1+df.n_pctChange/100))*100
        df = df.rename(columns = {"Date": "t_date", "c_bondCode":"c_code"})
        df[["t_date", "c_code", "value"]] =factor_pre(df[["t_date", "c_code", "value"]])
        return df[["t_date", "c_code", "value"]]
    
    def fIV_BS(self):
        #隐含波动率IV_BS
        df = self.df_after[["Date", "c_bondCode", "n_implied_vol", "n_close"]]
        df = df.rename(columns={"Date":"t_date", "c_bondCode":"c_code", "n_implied_vol":"value"})
        df[["t_date", "c_code", "value"]] = factor_pre(df[["t_date", "c_code", "value"]])
        return df[["t_date", "c_code", "value"]]  
    
    def ts_IV_BS(self, ND):
        # 隐含波动率IV_BS分位数
        df = self.df_after[["Date", "c_bondCode", "n_implied_vol", "n_close"]]
        df_valuation_stock = self.df_valuation_stock[["Date", "c_bondCode", "n_implied_vol", "n_close"]].copy()
        df_valuation_stock = df_valuation_stock.set_index('Date')
        # 滚动ND个数据点计算
        min_max_cp = df_valuation_stock.groupby('c_bondCode').rolling(ND).agg({'n_implied_vol': ['min', 'max']}).reset_index()
        min_max_cp.columns = ['c_bondCode', 'Date', 'min_implied_vol', 'max_implied_vol']
        df = pd.merge(df, min_max_cp, on=['c_bondCode', 'Date'], how='left')
        df['ts_IV_BS_roll{}d'.format(ND)] = (df['n_implied_vol'] - df['min_implied_vol']) / (df['max_implied_vol'] - df['min_implied_vol'])
        df = df.rename(columns={'Date': 't_date', 'c_bondCode': 'c_code', 'ts_IV_BS_roll{}d'.format(ND): 'value'})
        return factor_pre(df[['t_date', 'c_code', 'value']])
    
    def fconvPremiumRatio(self):
        #转股溢价率convPremiumRatio
        df = self.df_after[["Date", "c_bondCode", "n_convPremiumRatio","n_close"]]
        df = df.rename(columns = {"Date": "t_date", "c_bondCode":"c_code", "n_convPremiumRatio":"value"})
        df[["t_date", "c_code", "value"]] =factor_pre(df[["t_date", "c_code", "value"]])
        return df[["t_date", "c_code", "value"]]
    
    def ts_conv_premium(self, ND):
        # 转股溢价率分位数 ts_conv_premium_rollNDtdays_1d
        df = self.df_after[['Date', 'c_bondCode', 'n_convPremiumRatio', 'n_close']].copy()
        df_valuation_stock = self.df_valuation_stock[['Date', 'c_bondCode', 'n_convPremiumRatio', 'n_close']].copy()
        df_valuation_stock = df_valuation_stock.set_index('Date')
        # 滚动ND个数据点计算
        min_max_cp = df_valuation_stock.groupby('c_bondCode').rolling(ND).agg({'n_convPremiumRatio': ['min', 'max']}).reset_index()
        min_max_cp.columns = ['c_bondCode', 'Date', 'min_convPremium', 'max_convPremium']
        df = pd.merge(df, min_max_cp, on=['c_bondCode', 'Date'], how='left')
        df['ts_conv_premium_roll{}tdays_1d'.format(ND)] = (df['n_convPremiumRatio'] - df['min_convPremium']) / (df['max_convPremium'] - df['min_convPremium'])
        df = df.rename(columns={'Date': 't_date', 'c_bondCode': 'c_code', 'ts_conv_premium_roll{}tdays_1d'.format(ND): 'value'})
        return factor_pre(df[['t_date', 'c_code', 'value']])
    
    def fbondPremiumRatio(self):
        #纯债溢价率bondPremiumRatio
        df = self.df_after[["Date", "c_bondCode", "n_bondPremiumRatio", "n_close"]]
        df = df.rename(columns={"Date":"t_date", "c_bondCode":"c_code", "n_bondPremiumRatio":"value"})
        df[["t_date", "c_code", "value"]] = factor_pre(df[["t_date", "c_code", "value"]])
        return df[["t_date", "c_code", "value"]]
    
    def ts_bond_premium(self, ND):
        # 纯债溢价率分位数 ts_bond_premium_rollNDtdays_1d
        df = self.df_after[['Date', 'c_bondCode', 'n_bondPremiumRatio', 'n_close']].copy()
        df_valuation_stock = self.df_valuation_stock[['Date', 'c_bondCode', 'n_bondPremiumRatio', 'n_close']].copy()
        df_valuation_stock = df_valuation_stock.set_index('Date')
        # 滚动ND个数据点计算
        min_max_cp = df_valuation_stock.groupby('c_bondCode').rolling(ND).agg({'n_bondPremiumRatio': ['min', 'max']}).reset_index()
        min_max_cp.columns = ['c_bondCode', 'Date', 'min_sec_bond_premium', 'max_sec_bond_premium']
        df = pd.merge(df, min_max_cp, on=['c_bondCode', 'Date'], how='left')
        df['ts_bond_premium_roll{}tdays_1d'.format(ND)] = (df['n_bondPremiumRatio'] - df['min_sec_bond_premium']) / (df['max_sec_bond_premium'] - df['min_sec_bond_premium'])
        df = df.rename(columns={'Date': 't_date', 'c_bondCode': 'c_code', 'ts_bond_premium_roll{}tdays_1d'.format(ND): 'value'})
        return factor_pre(df[['t_date', 'c_code', 'value']])
    
    def fytm_cb(self):
        # 纯债到期收益率ytm_cb
        df = self.df_after[["Date", "c_bondCode", "n_ytmCB", "n_close"]]
        df = df.rename(columns={"Date": "t_date", "c_bondCode": "c_code", "n_ytmCB": "value"})
        df[["t_date", "c_code", "value"]] = factor_pre(df[["t_date", "c_code", "value"]])
        return df[["t_date", "c_code", "value"]]
    
    def ts_ytm(self, ND):
        # 纯债到期收益率ytm_cb分位数
        df = self.df_after[["Date", "c_bondCode", "n_ytmCB", "n_close"]].copy()
        df_valuation_stock = self.df_valuation_stock[["Date", "c_bondCode", "n_ytmCB", "n_close"]].copy()
        df_valuation_stock = df_valuation_stock.set_index('Date')
        # 滚动ND个数据点计算
        min_max_cp = df_valuation_stock.groupby('c_bondCode').rolling(ND).agg({'n_ytmCB': ['min', 'max']}).reset_index()
        min_max_cp.columns = ['c_bondCode', 'Date', 'min_ytmCB', 'max_ytmCB']
        df = pd.merge(df, min_max_cp, on=['c_bondCode', 'Date'], how='left')
        df['ts_ytm_roll{}d'.format(ND)] = (df['n_ytmCB'] - df['min_ytmCB']) / (df['max_ytmCB'] - df['min_ytmCB'])
        df = df.rename(columns={'Date': 't_date', 'c_bondCode': 'c_code', 'ts_ytm_roll{}d'.format(ND): 'value'})
        return factor_pre(df[['t_date', 'c_code', 'value']])

    def fdouble_low(self):
        #双低double_low
        df = self.df_after[["Date", "c_bondCode", "n_close", "n_convPremiumRatio"]]
        df = df.copy()
        df["value"] = df.n_close * df.n_convPremiumRatio
        df = df.rename(columns = {"Date": "t_date", "c_bondCode":"c_code"})
        df[["t_date", "c_code", "value"]] =factor_pre(df[["t_date", "c_code", "value"]])
        return df[["t_date", "c_code", "value"]]
    
    def ts_double_low(self, ND):
        # 双低分位数 ts_double_low_rollNDtdays_1m
        df = self.df_after[['Date', 'c_bondCode', 'n_close', 'n_convPremiumRatio']].copy()
        df['sec_double_low'] = df['n_convPremiumRatio'] * df['n_close']
        df_valuation_stock = self.df_valuation_stock[['Date', 'c_bondCode', 'n_close', 'n_convPremiumRatio']].copy()
        df_valuation_stock['sec_double_low'] = df_valuation_stock['n_convPremiumRatio'] *df_valuation_stock['n_close']
        df_valuation_stock = df_valuation_stock.set_index('Date')
        # 滚动ND个数据点计算
        min_max_cp = df_valuation_stock.groupby('c_bondCode').rolling(ND).agg({'sec_double_low': ['min', 'max']}).reset_index()
        min_max_cp.columns = ['c_bondCode', 'Date', 'min_double_low', 'max_double_low']
        df = pd.merge(df, min_max_cp, on=['c_bondCode', 'Date'], how='left')
        df['ts_double_low_roll{}tdays_1d'.format(ND)] = (df['sec_double_low'] - df['min_double_low']) / (df['max_double_low'] - df['min_double_low'])
        df = df.rename(columns={'Date': 't_date', 'c_bondCode': 'c_code', 'ts_double_low_roll{}tdays_1d'.format(ND): 'value'})
        return factor_pre(df[['t_date', 'c_code', 'value']])
    
    def fprice_delta(self):
        #涨跌幅差 price_delta
        df = self.df_after[["Date", "c_bondCode", "n_close", "n_pctChange", "S_DQ_PCTCHANGE"]]
        df = df.copy()
        df["value"] = df.n_pctChange - df.S_DQ_PCTCHANGE
        df = df.rename(columns = {"Date": "t_date", "c_bondCode":"c_code"})
        df[["t_date", "c_code", "value"]] =factor_pre(df[["t_date", "c_code", "value"]])
        return df[["t_date", "c_code", "value"]]
    
    def ts_ret_delta(self, ND):
        # 涨跌幅差分位数 ts_ret_delta_rollNDtdays_1d
        df = self.df_after[['Date', 'c_bondCode', 'n_close', 'n_pctChange', 'S_DQ_PCTCHANGE']].copy()
        df['sec_price_delta'] = df['n_pctChange'] - df['S_DQ_PCTCHANGE']
        df_valuation_stock = self.df_valuation_stock[['Date', 'c_bondCode', 'n_close', 'n_pctChange', 'S_DQ_PCTCHANGE']].copy()
        df_valuation_stock['sec_price_delta'] = df_valuation_stock['n_pctChange'] - df_valuation_stock['S_DQ_PCTCHANGE']
        df_valuation_stock = df_valuation_stock.set_index('Date')
        # 滚动ND个数据点计算
        min_max_cp = df_valuation_stock.groupby('c_bondCode').rolling(ND).agg({'sec_price_delta': ['min', 'max']}).reset_index()
        min_max_cp.columns = ['c_bondCode', 'Date', 'min_price_delta', 'max_price_delta']
        df = pd.merge(df, min_max_cp, on=['c_bondCode', 'Date'], how='left')
        df['ts_price_delta_roll{}tdays_1d'.format(ND)] = (df['sec_price_delta'] - df['min_price_delta']) / (df['max_price_delta'] - df['min_price_delta'])
        df = df.rename(columns={'Date': 't_date', 'c_bondCode': 'c_code', 'ts_price_delta_roll{}tdays_1d'.format(ND): 'value'})
        return factor_pre(df[['t_date', 'c_code', 'value']])
    
    def ts_volume(self, ND): # N=10
        # 成交量分位数因子
        df = self.df_after.copy()
        df_valuation_stock = self.df_valuation_stock
        df_valuation_stock = df_valuation_stock.set_index('Date')
        # 滚动ND个数据点计算
        min_max_cp = df_valuation_stock.groupby('c_bondCode').rolling(ND).agg({'n_volume': ['min', 'max']}).reset_index()
        min_max_cp.columns = ['c_bondCode', 'Date', 'min_volumn', 'max_volumn']
        df = pd.merge(df, min_max_cp, on=['c_bondCode', 'Date'], how='left')
        df['ts_volume_roll{}d'.format(ND)] = (df['n_volume'] - df['min_volumn']) / (df['max_volumn'] - df['min_volumn'])
        df = df.rename(columns={'Date': 't_date', 'c_bondCode': 'c_code', 'ts_volume_roll{}d'.format(ND): 'value'})
        return factor_pre(df[["t_date", "c_code", "value"]])
    

#%% 复杂转债类因子
    def fpremium_modified(self): # 修正溢价率
        df_valuation_stock = self.df_valuation_stock
        df = self.df_after      
        last_day = self.last_day
        df["value"] = np.zeros(shape = [df.shape[0], 1])
        for i in tqdm(range(len(last_day))):
            temp_data = df[df.Date == last_day[i]]
            y_data = temp_data.n_convPremiumRatio
            #temp_data.n_convValue[temp_data.n_convValue == 0] = temp_data.n_convValue[temp_data.n_convValue == 0] + 0.00001
            x_data = 1 / temp_data.n_convValue
            x_data = sm.add_constant(x_data)
            a,b = sm.OLS(y_data, x_data).fit().params
            new_x_data = a + b*x_data.iloc[:, 1]
            df["value"][df.Date == last_day[i]] = temp_data.n_convPremiumRatio - new_x_data
        df = df.rename(columns = {"Date": "t_date", "c_bondCode":"c_code"})
        df[["t_date", "c_code", "value"]] = factor_pre(df[["t_date", "c_code", "value"]])
        return df[["t_date", "c_code", "value"]]
    
    def ts_modified_premium(self, ND):
        # 修正溢价率分位数 ts_modified_premium_rollNDtdays_1d
        def get_conv_data(df_slice, n):
            # 获得历史转股价值和转股溢价率数据
            df_slice = df_slice.reset_index(drop=True)
            length = len(df_slice)
            if len(df_slice) < n:
                lst1, lst2 = [np.nan] * length, [np.nan] * length
            else:
                lst1, lst2 = [np.nan] * (n - 1), [np.nan] * (n - 1)
                lst_convValue = df_slice['n_convValue'].values
                lst_convPremium = df_slice['n_convPremiumRatio'].values
                for k in range(n - 1, length):
                    lst1.append(lst_convValue[k - (n - 1):k - (n - 1) + n])
                    lst2.append(lst_convPremium[k - (n - 1):k - (n - 1) + n])
            df_slice['all_convValue'] = lst1
            df_slice['all_convPremium'] = lst2
            return df_slice[['Date', 'c_bondCode', 'all_convValue', 'all_convPremium']]

        def modified_premium_cal(df_slice, n):
            # 计算修正溢价率分位数
            def conv_ols(x, y):
                # ols回归
                x = sm.add_constant(1 / x)
                a, b = sm.OLS(y, x).fit().params
                new_x = a + b * x.T[1]
                return y - new_x
            convValue = np.array([list(df_slice['all_convValue'].values[i]) for i in range(len(df_slice))]).T
            convValue[convValue == 0] = 0.00001
            convPremium = np.array([list(df_slice['all_convPremium'].values[i]) for i in range(len(df_slice))]).T
            all_mp = np.array(list(map(conv_ols, convValue, convPremium))).T
            max_mp, min_mp = np.max(all_mp, axis=1), np.min(all_mp, axis=1)
            # 计算当期分位数
            df_slice['ts_modified_premium_roll{}tdays'.format(n)] = list(map(lambda x, y, z: (x - y) / (z - y), all_mp.T[-1], min_mp, max_mp))
            df_slice['ts_modified_premium'] = list(all_mp.T[-1])
            df_slice['min'] = min_mp
            df_slice['max'] = max_mp
            return df_slice

        df = self.df_after.copy()
        df_valuation_stock = self.df_valuation_stock.copy()
        tqdm.pandas(desc='apply')
        # 获取历史20天的回归数据
        ols_data = df_valuation_stock.groupby('c_bondCode').progress_apply(lambda x: get_conv_data(x, ND)).reset_index(drop=True)
        df = pd.merge(df, ols_data, on=['c_bondCode', 'Date'], how='left')
        # 除去历史数据不满20天的 
        df = df.dropna(axis=0, subset=['all_convValue']).reset_index(drop=True)
        # 计算修正溢价率及其分位数
        df = df.groupby('Date').progress_apply(lambda x: modified_premium_cal(x, ND)).reset_index(drop=True)
        df = df.rename(columns={'Date': 't_date', 'c_bondCode': 'c_code','ts_modified_premium_roll{}tdays'.format(ND): 'value'})
        return factor_pre(df[['t_date', 'c_code', 'value']])
    
    def fpct_std(self, ND): # N=5 波动率
        df_valuation_stock = self.df_valuation_stock
        df_after = self.df_after      
        last_day = self.last_day
        def pct_std(N,df_valuation_stock,last_day):
            res = pd.DataFrame()
            for i in tqdm(range(len(last_day))):
                code = df_valuation_stock.loc[df_valuation_stock.Date==last_day[i],["c_bondCode"]]
                dt = df_valuation_stock.loc[(df_valuation_stock.Date<=last_day[i]),["Date", "c_bondCode","n_pctChange"]]
                dt =  dt[dt["c_bondCode"].isin(code.c_bondCode)]
                temp1 = dt.groupby("c_bondCode").count().reset_index()
                #temp1 = temp1["Date"]
                temp1=temp1.rename(columns ={"Date": "nCount"})
                temp2 = dt.groupby("c_bondCode").tail(N)
                temp2 = temp2.groupby("c_bondCode").n_pctChange.std().reset_index()
                temp2=temp2.rename(columns ={"n_pctChange": "pctstd_{}D".format(N)})
                temp = pd.merge(temp2, temp1, on =["c_bondCode"], how = "left")
                temp = temp.loc[temp.nCount >= N, ["c_bondCode", "pctstd_{}D".format(N)]]
                temp["pctstd_{}D".format(N)] = temp["pctstd_{}D".format(N)]
                temp["Date"] = np.full([temp.shape[0]],last_day[i])
                res = pd.concat([res, temp])
                #print(i)
            return res    
        r = pct_std(ND,df_valuation_stock,last_day)
        df = pd.merge(df_after,r,on =["c_bondCode", "Date"], how="left")
        df = df.rename(columns = {"Date": "t_date", "c_bondCode":"c_code", "pctstd_{}D".format(ND): "value"})
        df[["t_date", "c_code", "value"]] =factor_pre(df[["t_date", "c_code", "value"]])
        return df[["t_date", "c_code", "value"]]
    
    def ts_volatility(self, MD, ND):
        # M=10 波动率分位数 ts_volatilityMDd_rollNDtdays_1d
        df = self.df_after.copy()
        df_valuation_stock = self.df_valuation_stock.copy()
        df_valuation_stock = df_valuation_stock.set_index('Date')
        # 滚动MD个数据点计算
        vol = df_valuation_stock.groupby('c_bondCode').rolling(MD)['n_pctChange'].std().reset_index()
        vol.columns = ['c_bondCode', 'Date', 'sec_volatility{}d'.format(MD)]
        df_valuation_stock = df_valuation_stock.reset_index()
        df_valuation_stock = pd.merge(df_valuation_stock, vol, on=['c_bondCode', 'Date'], how='left')
        df = pd.merge(df, vol, on=['c_bondCode', 'Date'], how='left')

        df_valuation_stock = df_valuation_stock.set_index('Date')
        # 滚动ND个数据点计算
        min_max_v = df_valuation_stock.groupby('c_bondCode').rolling(ND).agg({'sec_volatility{}d'.format(MD): ['min', 'max']}).reset_index()
        min_max_v.columns = ['c_bondCode', 'Date', 'min_volatility', 'max_volatility']
        df = pd.merge(df, min_max_v, on=['c_bondCode', 'Date'], how='left')
        df['ts_volatility{}d_roll{}tdays_1d'.format(MD, ND)] = (df['sec_volatility{}d'.format(MD)] - df['min_volatility']) / (df['max_volatility'] - df['min_volatility'])
        df = df.rename(columns={'Date': 't_date', 'c_bondCode': 'c_code', 'ts_volatility{}d_roll{}tdays_1d'.format(MD, ND): 'value'})
        return factor_pre(df[['t_date', 'c_code', 'value']])
        
    def fweight_skew(self, ND): # N = 63
        #转债价格偏度因子
        df_valuation_stock = self.df_valuation_stock
        df_after = self.df_after      
        last_day = self.last_day
        def weight_skew(N, df_valuation_stock,last_day):
            res = pd.DataFrame()
            for i in tqdm(range(len(last_day))):
                code = df_valuation_stock.loc[df_valuation_stock.Date==last_day[i],["c_bondCode"]]
                dt = df_valuation_stock.loc[(df_valuation_stock.Date<=last_day[i]),["Date", "c_bondCode","n_close","n_volume"]]
                dt =  dt[dt["c_bondCode"].isin(code.c_bondCode)]
                temp1 = dt.groupby("c_bondCode").count().reset_index()
                #temp1 = temp1["Date"]
                temp1=temp1.rename(columns ={"Date": "nCount"})
                #print(temp1)
                temp2 = dt.groupby("c_bondCode").tail(N)
                temp3 = temp2.groupby("c_bondCode").n_close.std().reset_index()
                temp3=temp3.rename(columns ={"n_close": "std"})
                #temp3 = dt.groupby("c_bondCode").tail(N)
                temp4 = temp2.groupby("c_bondCode").n_close.mean().reset_index()
                temp4 = temp4.rename(columns ={"n_close": "mean"})
                temp5 = temp2.groupby("c_bondCode").n_volume.sum().reset_index()
                temp5 = temp5.rename(columns ={"n_volume": "sum"})
                temp = pd.merge(temp2, temp1[["c_bondCode",'nCount']], on =["c_bondCode"], how = "left")
                temp = pd.merge(temp, temp3[["c_bondCode","std"]], on =["c_bondCode"], how = "left")
                temp = pd.merge(temp, temp4[["c_bondCode","mean"]], on =["c_bondCode"], how = "left")
                temp = pd.merge(temp, temp5[["c_bondCode","sum"]], on =["c_bondCode"], how = "left")
                temp = temp[temp.nCount >= N]
                temp["std"] = temp["std"]
                temp["weight_skew"] = temp["n_volume"]/temp["sum"] * (temp["n_close"] - temp["mean"]) ** 3 / temp["std"]**(3)
                temp_dt = temp.loc[:,["Date", "c_bondCode","weight_skew"]]
                subset = temp_dt.groupby("c_bondCode").tail(N)
                subset = subset.groupby("c_bondCode").weight_skew.sum().reset_index()
                subset = subset.rename(columns = {"weight_skew": "weightskew_{}D".format(N)})
                subset["Date"] = np.full([subset.shape[0]],last_day[i])
                res = pd.concat([res, subset])
            return res
        df = pd.merge(df_after,weight_skew(ND,df_valuation_stock,last_day),on =["c_bondCode", "Date"], how="left")
        df = df.rename(columns = {"Date": "t_date", "c_bondCode":"c_code", "weightskew_{}D".format(ND): "value"})
        df[["t_date", "c_code", "value"]] =factor_pre(df[["t_date", "c_code", "value"]])
        return df[["t_date", "c_code", "value"]]
    
    def fprice_ratio(self, ND): # N=5 价格强弱因子
        df_valuation_stock = self.df_valuation_stock
        df_after = self.df_after      
        last_day = self.last_day
        def price_ratio(N,df_valuation_stock,last_day):
            res = pd.DataFrame()
            for i in tqdm(range(len(last_day))):
                code = df_valuation_stock.loc[df_valuation_stock.Date==last_day[i],["c_bondCode"]]
                dt = df_valuation_stock.loc[(df_valuation_stock.Date<=last_day[i]),["Date", "c_bondCode","n_close"]]
                dt = dt[dt["c_bondCode"].isin(code.c_bondCode)]
                temp1 = dt.groupby("c_bondCode").count().reset_index()
                temp1 = temp1.rename(columns ={"Date": "nCount"})
                temp2 = dt.groupby("c_bondCode").tail(N)
                temp2 = temp2.groupby("c_bondCode").n_close.mean().reset_index()
                temp2=temp2.rename(columns ={"n_close": "n_close_mean"})
                temp = pd.merge(temp2, temp1, on =["c_bondCode"], how = "left")
                temp = temp.loc[temp.nCount >= N, ["c_bondCode", "n_close_mean"]]
                temp["Date"] = np.full([temp.shape[0]],last_day[i])
                res = pd.concat([res, temp])
            return res
        r = price_ratio(ND,df_valuation_stock,last_day)
        df = pd.merge(df_after,r,on =["c_bondCode", "Date"], how="left")
        df["value"] = df.n_close / df.n_close_mean - 1
        df = df.rename(columns = {"Date": "t_date", "c_bondCode":"c_code"})
        df[["t_date", "c_code", "value"]] =factor_pre(df[["t_date", "c_code", "value"]])
        return df[["t_date", "c_code", "value"]]
    
    def fvol_firm(self, ND): # N=10 成交量稳健性因子
        df_valuation_stock = self.df_valuation_stock
        df_after = self.df_after      
        last_day = self.last_day
        def vol_firm(N,df_valuation_stock,last_day):
            res = pd.DataFrame()
            for i in tqdm(range(len(last_day))):
                code = df_valuation_stock.loc[df_valuation_stock.Date==last_day[i],["c_bondCode"]]
                dt = df_valuation_stock.loc[(df_valuation_stock.Date<=last_day[i]),["Date", "c_bondCode","n_volume"]]
                dt = dt[dt["c_bondCode"].isin(code.c_bondCode)]
                temp1 = dt.groupby("c_bondCode").count().reset_index()
                temp1 = temp1.rename(columns ={"Date": "nCount"})
                temp2 = dt.groupby("c_bondCode").tail(N)
                temp3 = temp2.groupby("c_bondCode").n_volume.mean().reset_index()
                temp3=temp3.rename(columns ={"n_volume": "mean"})
                temp4 = temp2.groupby("c_bondCode").n_volume.std().reset_index()
                temp4=temp4.rename(columns ={"n_volume": "std"})
                temp = pd.merge(temp3, temp1, on =["c_bondCode"], how = "left")
                temp = pd.merge(temp, temp4[["c_bondCode","std"]], on =["c_bondCode"], how = "left")
                temp["vol_firm_{}D".format(N)] = temp["mean"] / temp["std"]
                temp = temp.loc[temp.nCount >= N, ["c_bondCode", "vol_firm_{}D".format(N)]]
                temp["Date"] = np.full([temp.shape[0]],last_day[i])
                res = pd.concat([res, temp])
            return res
        r = vol_firm(ND,df_valuation_stock,last_day)
        df = pd.merge(df_after,r,on =["c_bondCode", "Date"], how="left")
        df = df.rename(columns = {"Date": "t_date", "c_bondCode":"c_code", "vol_firm_{}D".format(ND): "value"})
        print(df[["t_date", "c_code", "value"]])
        df[["t_date", "c_code", "value"]] =factor_pre(df[["t_date", "c_code", "value"]])
        print(df[["value","n_close","t_date"]])
        return df[["t_date", "c_code", "value"]]
    
    def fIV_difference(self):
        # 隐波差IV_difference
        df_valuation_stock = self.df_valuation_stock
        df_after = self.df_after
        last_day = self.last_day
        
        def IV_difference(df_valuation_stock, last_day):
            # 寻找正股过去一年月波动率中位数
            def find_middle(df):
                temp = pd.DataFrame({"temp": pd.qcut(df.Date, 12, [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12])})
                df = pd.concat([df, temp], axis=1)
                vol = df.groupby("temp", observed=True).S_DQ_PCTCHANGE.std().reset_index()
                df = df.copy()
                df["S_DQ_PCTCHANGE"].iloc[-1] = vol["S_DQ_PCTCHANGE"].median()*100
                return df.tail(1)
            # 过滤过去交易日不满1年的(12*21)
            def filter_low(df):
                return df['Date'].size >= 252
            res = pd.DataFrame()
            for i in tqdm(range(len(last_day))):
                N = 21 * 12
                # 筛选符合条件的债券代码和数据
                code = df_valuation_stock.loc[df_valuation_stock.Date == last_day[i], ["c_bondCode"]]
                dt = df_valuation_stock.loc[(df_valuation_stock.Date <= last_day[i]), ["Date", "c_bondCode", "S_DQ_PCTCHANGE"]]
                dt = dt[dt["c_bondCode"].isin(code.c_bondCode)]
                dt['S_DQ_PCTCHANGE'] = dt['S_DQ_PCTCHANGE'] / 100 * math.sqrt(252) # 单位换算并且折算成年化
                # 往前递推21*12个交易日并分别计算月度波动率
                middle_vol = dt.groupby("c_bondCode").tail(N)
                middle_vol = middle_vol.groupby("c_bondCode").filter(filter_low)
                if not middle_vol.empty:
                    middle_vol = middle_vol.groupby("c_bondCode", as_index=False).apply(find_middle)
                    middle_vol = middle_vol.rename(columns={"S_DQ_PCTCHANGE": "middle_vol"})
                    res = pd.concat([res, middle_vol])
            return res
        
        r = IV_difference(df_valuation_stock, last_day)
        df = pd.merge(df_after, r, on=["c_bondCode", "Date"], how="left")
        # df_after中已存在c_code列，因此先提取所需列df1，再重命名
        df1 = df[["Date", "c_bondCode", "middle_vol", "n_implied_vol", "n_close"]]
        df1 = df1.copy()
        df1["value"] = df1['n_implied_vol'] - df1["middle_vol"]
        df1 = df1.rename(columns={"Date": "t_date", "c_bondCode": "c_code"})
        df1[["t_date", "c_code", "value"]] = factor_pre(df1[["t_date", "c_code", "value"]])
        return df1[["t_date", "c_code", "value"]]
    
    def ts_IV_delta_roll(self, ND):
        # 隐波差IV_difference分位数
        df_valuation_stock = self.df_valuation_stock
        df_after = self.df_after
        freq = self.freq
        last_day = df_valuation_stock['Date'].unique()
        last_day = last_day[list(range(len(last_day)-len(df_after.Date.unique())-ND,len(last_day),freq))]
        
        def IV_difference(df_valuation_stock, last_day):
            # 寻找正股过去一年月波动率中位数
            def find_middle(df):
                temp = pd.DataFrame({"temp": pd.qcut(df.Date, 12, [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12])})
                df = pd.concat([df, temp], axis=1)
                vol = df.groupby("temp", observed=True).S_DQ_PCTCHANGE.std().reset_index()
                df = df.copy()
                df["S_DQ_PCTCHANGE"].iloc[-1] = vol["S_DQ_PCTCHANGE"].median()*100
                return df.tail(1)
            # 过滤过去交易日不满1年的(12*21)
            def filter_low(df):
                return df['Date'].size >= 252
            res = pd.DataFrame()
            for i in tqdm(range(len(last_day))):
                N = 21 * 12
                # 筛选符合条件的债券代码和数据
                code = df_valuation_stock.loc[df_valuation_stock.Date == last_day[i], ["c_bondCode"]]
                dt = df_valuation_stock.loc[(df_valuation_stock.Date <= last_day[i]), ["Date", "c_bondCode", "S_DQ_PCTCHANGE"]]
                dt = dt[dt["c_bondCode"].isin(code.c_bondCode)]
                dt['S_DQ_PCTCHANGE'] = dt['S_DQ_PCTCHANGE'] / 100 * math.sqrt(252) # 单位换算并且折算成年化
                # 往前递推21*12个交易日并分别计算月度波动率
                middle_vol = dt.groupby("c_bondCode").tail(N)
                middle_vol = middle_vol.groupby("c_bondCode").filter(filter_low)
                if not middle_vol.empty:
                    middle_vol = middle_vol.groupby("c_bondCode", as_index=False).apply(find_middle)
                    middle_vol = middle_vol.rename(columns={"S_DQ_PCTCHANGE": "middle_vol"})
                    res = pd.concat([res, middle_vol])
            return res
        
        r = IV_difference(df_valuation_stock, last_day).reset_index(drop=True)
        df = pd.merge(df_valuation_stock, r, on=["c_bondCode", "Date"], how="left")
        df["IV_delta"] = df['n_implied_vol'] - df["middle_vol"]
        
        # 滚动ND个数据点计算
        df = df[['Date','c_bondCode','IV_delta']].set_index('Date')
        min_max_cp = df.groupby('c_bondCode').rolling(ND).agg({'IV_delta': ['min', 'max']}).reset_index()
        min_max_cp.columns = ['c_bondCode', 'Date', 'min_IV_delta', 'max_IV_delta']
        df = pd.merge(df.reset_index(), min_max_cp, on=['c_bondCode', 'Date'], how='left')
        df = pd.merge(df_after, df, on=['c_bondCode', 'Date'], how='left')
        df['ts_IV_delta_roll{}d'.format(ND)] = (df['IV_delta'] - df['min_IV_delta']) / (df['max_IV_delta'] - df['min_IV_delta'])
        df = df.rename(columns={'Date': 't_date', 'c_bondCode': 'c_code', 'ts_IV_delta_roll{}d'.format(ND): 'value'})
        return factor_pre(df[["t_date", "c_code", "value"]])

    
    def fbond_return(self, ND): # ND = 10
        # 收益率bond_return
        df_valuation_stock = self.df_valuation_stock
        df_after = self.df_after
        last_day = self.last_day
        
        def bond_return(N, df_valuation_stock, last_day):
            res = pd.DataFrame()
            for i in tqdm(range(len(last_day))):
                # 筛选符合条件的债券代码和数据
                code = df_valuation_stock.loc[df_valuation_stock.Date == last_day[i], ["c_bondCode"]]
                dt = df_valuation_stock.loc[(df_valuation_stock.Date <= last_day[i]), ["Date", "c_bondCode", "n_pctChange"]]
                dt = dt[dt["c_bondCode"].isin(code.c_bondCode)]
                # 时间区间内债券出现的次数
                count = dt.groupby("c_bondCode").count().reset_index()
                count = count.rename(columns={"Date": "nCount"})
                # 债券平均收益率
                ret = dt.groupby("c_bondCode").tail(N)
                ret = ret.groupby("c_bondCode").n_pctChange.mean().reset_index()
                ret = ret.rename(columns={"n_pctChange": "bond_return_{}D".format(N)})
                # 合并表
                temp = pd.merge(ret, count, on=["c_bondCode"], how="left")
                temp = temp.loc[temp.nCount >= N, ["c_bondCode", "bond_return_{}D".format(N)]]
                temp['Date'] = np.full([temp.shape[0]], last_day[i])
                res = pd.concat([res, temp])
            return res
        
        r = bond_return(ND, df_valuation_stock, last_day)
        df = pd.merge(df_after, r, on=["c_bondCode", "Date"], how="left")
        # df_after中已存在c_code列，因此先提取所需列df1，再重命名
        df1 = df[["Date", "c_bondCode", "bond_return_{}D".format(ND), "n_close"]]
        df1 = df1.rename(columns={"Date": "t_date", "c_bondCode": "c_code", "bond_return_{}D".format(ND): "value"})
        df1[["t_date", "c_code", "value"]] = factor_pre(df1[["t_date", "c_code", "value"]])
        return df1[["t_date", "c_code", "value"]] 
    
    def ts_ret_roll(self, ND): # ND = 20
        # 收益率bond_return
        df = self.df_after.copy()
        df_valuation_stock = self.df_valuation_stock
        df_valuation_stock = df_valuation_stock.set_index('Date')
        # 滚动ND个数据点计算
        min_max_cp = df_valuation_stock.groupby('c_bondCode').rolling(ND).agg({'n_pctChange': ['min', 'max']}).reset_index()
        min_max_cp.columns = ['c_bondCode', 'Date', 'min_pctChange', 'max_pctChange']
        df = pd.merge(df, min_max_cp, on=['c_bondCode', 'Date'], how='left')
        df['ts_ret_roll{}d'.format(ND)] = (df['n_pctChange'] - df['min_pctChange']) / (df['max_pctChange'] - df['min_pctChange'])
        df = df.rename(columns={'Date': 't_date', 'c_bondCode': 'c_code', 'ts_ret_roll{}d'.format(ND): 'value'})
        return factor_pre(df[["t_date", "c_code", "value"]])
    
#%% 正股类因子
    def fstock_mv(self):
        #股票市值stock_mv
        df = self.df_after[["Date", "c_bondCode", "S_VAL_MV","n_close"]]
        df = df.rename(columns = {"Date": "t_date", "c_bondCode":"c_code", "S_VAL_MV":"value"})
        df[["t_date", "c_code", "value"]] =factor_pre(df[["t_date", "c_code", "value"]])
        return df[["t_date", "c_code", "value"]]    
    
    def fEP_net_ttm(self):
        #归母净利润 EP_net_ttm
        df = self.df_after[["Date", "c_bondCode", "S_DFA_NETPROFIT_TTM", "S_VAL_MV","n_close"]]
        df = df.copy()
        df["value"] = df["S_DFA_NETPROFIT_TTM"]/df["S_VAL_MV"]/10000
        df = df.rename(columns = {"Date": "t_date", "c_bondCode":"c_code"})
        df[["t_date", "c_code", "value"]] =factor_pre(df[["t_date", "c_code", "value"]])
        return df[["t_date", "c_code", "value"]]

    def fEP_deducted_ttm(self):
        #扣除非经常性损益后的净利润 EP_deducted_ttm
        df = self.df_after[["Date", "c_bondCode", "S_DFA_DEDUCTEDPROFIT_TTM", "S_VAL_MV","n_close"]]
        df = df.copy()
        df["value"] = df["S_DFA_DEDUCTEDPROFIT_TTM"]/df["S_VAL_MV"]/10000
        df = df.rename(columns = {"Date": "t_date", "c_bondCode":"c_code"})
        df[["t_date", "c_code", "value"]] =factor_pre(df[["t_date", "c_code", "value"]])
        return df[["t_date", "c_code", "value"]]

    def fstock_return(self, ND): # N=20 正股收益率
        df_valuation_stock = self.df_valuation_stock
        df_after = self.df_after      
        last_day = self.last_day
        def stock_return(N,df_valuation_stock,last_day):
            res = pd.DataFrame()
            for i in tqdm(range(len(last_day))):
                code = df_valuation_stock.loc[df_valuation_stock.Date==last_day[i],["c_bondCode"]]
                dt = df_valuation_stock.loc[(df_valuation_stock.Date<=last_day[i]),["Date", "c_bondCode","S_DQ_PCTCHANGE"]]
                dt = dt[dt["c_bondCode"].isin(code.c_bondCode)]
                temp1 = dt.groupby("c_bondCode").count().reset_index()
                temp1 = temp1.rename(columns ={"Date": "nCount"})
                temp2 = dt.groupby("c_bondCode").tail(N)
                temp2 = temp2.groupby("c_bondCode").S_DQ_PCTCHANGE.mean().reset_index()
                temp2=temp2.rename(columns ={"S_DQ_PCTCHANGE": "stock_return_{}D".format(N)})
                temp = pd.merge(temp2, temp1, on =["c_bondCode"], how = "left")
                temp = temp.loc[temp.nCount >= N, ["c_bondCode", "stock_return_{}D".format(N)]]
                temp["Date"] = np.full([temp.shape[0]],last_day[i])
                res = pd.concat([res, temp])
            return res
        r = stock_return(ND,df_valuation_stock,last_day)
        df = pd.merge(df_after,r,on =["c_bondCode", "Date"], how="left")
        df = df.rename(columns = {"Date": "t_date", "c_bondCode":"c_code", "stock_return_{}D".format(ND): "value"})
        df[["t_date", "c_code", "value"]] =factor_pre(df[["t_date", "c_code", "value"]])
        print(df[["value","n_close","t_date"]])
        #draw_data(df[["value","n_close","t_date"]], "stock_return_{}D".format(ND))
        return df[["t_date", "c_code", "value"]]
    
    def fsue(self, YN): # YN=0 净利润(不含少数股东损益); YN=1 扣除非经常性损益后净利润（扣除少数股东损益）
        data_all = self.df_after
        date_all = data_all["Date"].unique()
        code_all = data_all["c_bondCode"].unique()
        
        # 从米筐提取转债正股对应信息
        rqdatac.init('license', 'AcBHy5_JJ6wjZdu7Q-ey7dX-J3BmyEC_KblY2Q_hBeOuoBaeBbgXTNSe6XZvqKVESbyUf7vMpLLGuO_aqyb3w9fWGI7q4wdClE6cMp_Z3N4PqqTHJ0nr3CIuXtk-5XzSD1p7NTdNcrAfZlRVpMMtY_PDC9FYuXNmC_EnuQg4H-A=fGk9EhHcK3xN189iXYSWLyiMdGUeXXlVZqr2MxhBypSHxQYnIIyxyM8BR8oNnVUdWhKx-ZrFRIjSONd7uYpOvpcBab92P60iAR_JopX61emtrvsY1xG_uCfYhDPBdDSJKaniJhTPuoBIU4JZun8-8fMIxzx7lnwBm2kAUOA_Mpg=')
        instrument = rqdatac.all_instruments(type='Convertible', market='cn', date=None)
        instrument = instrument.dropna(subset=["stock_code"])
        code_dict = dict(zip(rqdatac.id_convert(list(instrument['order_book_id']),to='normal'),\
                             rqdatac.id_convert(list(instrument['stock_code']),to='normal')))
        
        # 提取利润相关数据
        def get_stock_data(bond_code):
            engine = create_engine('mysql+pymysql://hs_wangwenjie:hs_wangwenjie#A0@192.168.201.181:3306/wind?charset=utf8')
            # A股基本信息
            stock_code = code_dict[bond_code]
            query = ("""select S_INFO_LISTDATE from AShareDescription where S_INFO_WINDCODE = '%s'""") % (stock_code)
            list_date = pd.read_sql_query(query, engine)
            # A股利润表 
            query = ("""select S_INFO_WINDCODE, STATEMENT_TYPE, ANN_DT, REPORT_PERIOD, NET_PROFIT_EXCL_MIN_INT_INC, NET_PROFIT_AFTER_DED_NR_LP
                         from AShareIncome 
                         where STATEMENT_TYPE='%s' and S_INFO_WINDCODE = '%s'
                         order by REPORT_PERIOD""") % ('408001000', stock_code)
            data = pd.read_sql_query(query, engine).sort_values(by=['S_INFO_WINDCODE','REPORT_PERIOD'])
            try:
                data = data[pd.to_datetime(data['REPORT_PERIOD']) >= pd.to_datetime(list_date.iloc[0,0])]
            except:
                pass
            data['bond_code'] = bond_code
            return data

        stk_data = []
        print('提取利润表数据')
        for i in tqdm(code_all):
            stk_data_i = get_stock_data(i)
            stk_data_i['年份'] = [x[0:4] for x in stk_data_i['REPORT_PERIOD']]
            stk_data_i['报告期'] = [x[4:8] for x in stk_data_i['REPORT_PERIOD']]
            stk_data_i['NET_PROFIT_EXCL_MIN_INT_INC(DJ)'] = np.nan
            stk_data_i['NET_PROFIT_AFTER_DED_NR_LP(DJ)'] = np.nan
            for j in range(len(stk_data_i['REPORT_PERIOD'])):
                if stk_data_i.iloc[j,-4] == stk_data_i.iloc[j-1,-4]:
                    stk_data_i.iloc[j,-2] = stk_data_i.iloc[j,4] - stk_data_i.iloc[j-1,4]
                    stk_data_i.iloc[j,-1] = stk_data_i.iloc[j,5] - stk_data_i.iloc[j-1,5]
                elif stk_data_i.iloc[j,-3] == '0331':
                    stk_data_i.iloc[j,-2] = stk_data_i.iloc[j,4]
                    stk_data_i.iloc[j,-1] = stk_data_i.iloc[j,5]
                else:
                    pass
            stk_data_i = stk_data_i[['S_INFO_WINDCODE', 'STATEMENT_TYPE', 'ANN_DT', 'REPORT_PERIOD',\
                                     'NET_PROFIT_EXCL_MIN_INT_INC(DJ)','NET_PROFIT_AFTER_DED_NR_LP(DJ)','bond_code']]
            #stk_data_i = stk_data_i.sort_values(by=['ANN_DT'])
            stk_data.append(stk_data_i)
        stk_data = pd.concat(stk_data)

        stk_data['ANN_DT'] = pd.to_datetime(stk_data['ANN_DT'])
        stk_data['REPORT_PERIOD'] = pd.to_datetime(stk_data['REPORT_PERIOD'])
        stk_data = stk_data.sort_values(by=['REPORT_PERIOD'])

        df_profit1 = stk_data.pivot('REPORT_PERIOD','bond_code','NET_PROFIT_EXCL_MIN_INT_INC(DJ)')/100000000 # 净利润(不含少数股东损益)
        df_profit2 = stk_data.pivot('REPORT_PERIOD','bond_code','NET_PROFIT_AFTER_DED_NR_LP(DJ)')/100000000 # 扣除非经常性损益后净利润（扣除少数股东损益）
        
        if YN == 0:
            df_profit = df_profit1
            factor_name = 'SUE'
        else:
            df_profit = df_profit2
            factor_name = 'SUE_after'
        # 测算漂移项
        def calculate_pyx(df_profit):
            print('计算漂移项')
            df_profit = df_profit[df_profit.index >= '2010-01-31 00:00:00']
            profit_change = df_profit - df_profit.shift(4)
            profit_change_avg = pd.DataFrame(index=profit_change.index, columns=profit_change.columns)
            for i in range(len(profit_change.index)-8):
                for j in range(len(profit_change.columns)):
                    profit_change_avg.iloc[i+8,j] = profit_change.iloc[i:i+8,j].mean()
            profit_change_avg = profit_change_avg.dropna(axis=0, how='all')
            return profit_change_avg

        # 测算标准差
        def calculate_bzc(df_profit):
            print('计算标准差')
            df_profit = df_profit[df_profit.index >= '2010-01-31 00:00:00']
            profit_change = df_profit - df_profit.shift(4)
            profit_change_std = pd.DataFrame(index=profit_change.index, columns=profit_change.columns)
            for i in range(len(profit_change.index)-8):
                for j in range(len(profit_change.columns)):
                    profit_change_std.iloc[i+8,j] = profit_change.iloc[i:i+8,j].std()
            profit_change_std = profit_change_std.dropna(axis=0, how='all')
            profit_change_std[abs(profit_change_std) <= 0.00001] = np.nan
            return profit_change_std

        # 测算SUE
        def calculate_sue(df_profit):
            df_profit = df_profit[df_profit.index >= '2010-01-31 00:00:00']
            profit_change_avg = calculate_pyx(df_profit)
            df_profit_shift = df_profit.shift(4)
            df_profit_shift = df_profit_shift[df_profit_shift.index >= profit_change_avg.index[0]]
            df_profit_expect = df_profit_shift + profit_change_avg
            df_sue = (df_profit[df_profit.index>=df_profit_expect.index[0]] - df_profit_expect) / calculate_bzc(df_profit)
            return df_sue
        
        def output_facor(df_profit):
            df_sue = calculate_sue(df_profit)
            df_sue_adjust = df_sue.copy()
            # 标准化
            df_mean = df_sue_adjust.mean(1)
            df_std = df_sue_adjust.std(1) 
            print('数据标准化')
            #for i in tqdm(df_sue_adjust.columns):
            #    df_sue_adjust.loc[:,i] = (df_sue_adjust.loc[:,i] - df_mean) / df_std
            for i in tqdm(df_sue_adjust.index):
                df_sue_adjust.loc[i,:] = (df_sue_adjust.loc[i,:] - df_mean[i]) / df_std[i]

            # 去极值
            mean = df_sue_adjust.mean(1)
            std = df_sue_adjust.std(1) 
            up = mean + 3*std       # 上限
            down = mean - 3*std     # 下限
            print('数据去极值')
            for i in tqdm(df_sue_adjust.index):
                df_sue_adjust.loc[i,:] = df_sue_adjust.loc[i,:].clip(up[i], down[i], axis=0) 
            df_sue = pd.DataFrame(df_sue.stack(),columns=['SUE因子-标准化前'])
            df_sue['SUE因子'] = pd.DataFrame(df_sue_adjust.stack())
            df_sue['日期'] = df_sue.index.get_level_values('REPORT_PERIOD')
            df_sue['转债代码'] = df_sue.index.get_level_values('bond_code')
            df_sue = df_sue[['日期','转债代码','SUE因子']]
            return df_sue

        def output_result(df_profit):
            stk_data.set_index(['REPORT_PERIOD', 'bond_code'], inplace=True)
            df_sue = output_facor(df_profit)
            df_sue['ANN_DT'] = stk_data['ANN_DT']
            df_sue = df_sue.sort_values(by=['日期'])
            df_sue = df_sue.drop_duplicates(['转债代码','ANN_DT'], keep='last').reset_index()
            
            sue_adj = df_sue.pivot('ANN_DT','bond_code','SUE因子') # 标准化后数据
            sue_adj = pd.DataFrame(index=pd.date_range(start=sue_adj.index[0],end=date_all[-1],freq='D')).\
                      merge(sue_adj,left_index=True,right_index=True,how='left')
            sue_adj = sue_adj.fillna(method='ffill') 
            sue_adj = pd.DataFrame(sue_adj.stack()).reset_index()
            sue_adj.columns = ['Date', 'c_bondCode', 'value']
            sue_adj = pd.merge(data_all,sue_adj,on =["c_bondCode", "Date"], how="left")
            sue_adj = sue_adj.rename(columns = {"Date": "t_date", "c_bondCode":"c_code"})
            return sue_adj
    
        df = output_result(df_profit)
        return df[["t_date", "c_code", "value"]]
    
#%% 结果输出
def output_database(fn, start_date, end_date, freq, first_time_to_sql=False):
    conf = {'username': 'hs_wangwenjie',
            'password': 'hs_wangwenjie#A0',
            'host': '192.168.201.185',
            'port': 3306,
            'db': 'wangwj'}
    url = 'mysql+pymysql://%s:%s@%s:%s/%s' % (conf['username'], conf['password'], conf['host'], conf['port'], conf['db'])
    engine = create_engine(url)
    query = ("""select DISTINCT t_date from conv_factor_new where factor_name = '%s' ORDER BY t_date""") % (fn)
    if first_time_to_sql:
        f_data = get_data.df_factor_data(fn, start_date, end_date, freq)
        f_data.insert(1, 'factor_name', fn)
        f_data.to_sql('conv_factor_new', engine, index=False, if_exists='append')
    else:
        last_date = pd.read_sql_query(query, engine).iloc[-1].tolist()[0]
        if last_date >= pd.to_datetime(end_date):
            pass
        else:
            # 仅计算数据库中无数据的部分
            f_data = get_data.df_factor_data(fn, max(last_date, pd.to_datetime(start_date)), end_date, freq)
            f_data = f_data[f_data.t_date > last_date]
            ndays = f_data.t_date.nunique()
            print('已更新{}日数据'.format(ndays))
            f_data.insert(1, 'factor_name', fn)
            f_data.to_sql('conv_factor_new', engine, index=False, if_exists='append')
    return

def output_database_supple(fn, start, end, supple_date, freq): # 单独补充缺失数据
    conf = {'username': 'hs_wangwenjie',
            'password': 'hs_wangwenjie#A0',
            'host': '192.168.201.185',
            'port': 3306,
            'db': 'wangwj'}
    url = 'mysql+pymysql://%s:%s@%s:%s/%s' % (conf['username'], conf['password'], conf['host'], conf['port'], conf['db'])
    engine = create_engine(url)
    f_data = get_data.df_factor_data(fn, start, end, freq)
    f_data = f_data[f_data.t_date == supple_date]
    f_data.insert(1, 'factor_name', fn)
    f_data.to_sql('conv_factor_new', engine, index=False, if_exists='append')
    return


if __name__ == '__main__':
    lst_sec = [
               'sec_convprice',
               'sec_convsize',
               'sec_conv_stocksize',
               'sec_ytm',
               'sec_bond_premium',
               'sec_IV_BS',
               'sec_IV_delta', # 备注:数据缺失(2018-01-02至2018-01-10)
               'sec_weightskew_63D',
               'sec_convturnover',
               'sec_volatility5d',
               'sec_conv_amplitude',
               'sec_volstability_10d',
               'sec_ret',
               'sec_priceratio_5d',
               'sec_convPremium',
               'sec_modified_premium',
               'sec_double_low',
               'sec_amplitude_delta',
               'sec_ret_delta',
               'sec_stock_mv',
               'sec_stock_ret_20D',
               'sec_EP_net_ttm', # 备注:数据缺失(2024-01-16至2024-01-19)(源数据问题)
               'sec_EP_deducted_ttm',  # 备注:数据缺失(2024-01-16至2024-01-19)(源数据问题)
               'sec_SUE',
               'sec_SUE_after'
               ]
        
    lst_ts = [
              'ts_convprice_roll20d',
              'ts_convturnover_roll20d',
              'ts_volatility5d_roll20d',
              'ts_amplitude_roll20d',
              'ts_conv_premium_roll20d',
              'ts_modified_premium_roll20d',
              'ts_double_low_roll20d',
              'ts_ret_delta_rol20d',
              'ts_bond_premium_roll20d',
              'ts_ret_roll20d',
              'ts_IV_delta_roll20d', # 备注:数据缺失(2018-01-02至2018-02-06; 2024-06-25至2024-06-28)
              'ts_IV_BS_roll20d', # 备注:数据缺失(2018-01-02至2018-01-26)
              'ts_ytm_roll20d',
              'ts_volumerank_roll10d'
               ]


    start_date = '2018-01-02'
    end_date = str(date.today())
    freq = 1
    print('更新截面类因子')
    for i in lst_sec:
        print('正在更新{}因子'.format(i))
        output_database(i, start_date, end_date, freq, first_time_to_sql=False)
    
    print('更新时序类因子')
    for i in lst_ts:
        print('正在更新{}因子'.format(i))
        output_database(i, start_date, end_date, freq, first_time_to_sql=False)
        
        